﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using Ebiz.libs;
using System.Web.Services;
using System.Web.Script.Services;

namespace Ebiz
{
    public partial class ApprovalRule : System.Web.UI.Page
    {
        public string RuleList;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack){
                RuleList = LoadRules();
            }
        }

        protected static string LoadRules(){

            string result = "";
            string strQuery = "SELECT IDControl, Operator1, Value1, Operator2, Value2, FixDisc, " +
                "COUNT(IDControl) AS TotalScenario " +
                "FROM tbl_ApprovalRule GROUP BY IDControl, Operator1, Value1, Operator2, Value2, FixDisc";

            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    string TRClass = "";
                    string TDStart = "<td>";
                    string TDStartLeft = "<td style='text-align:left;'>";
                    string TDEnd = "</td>";
                    string TREnd = "</td></tr>";
                    string Imgs = "<img src='images/info.png' class='btnView' alt='Details' title='Details' />";
                    string Rows = "";

                    int idx = 0;
                    while (sqlReader.Read()){
                        idx++;
                        string IDControl = sqlReader.GetValue(0).ToString();
                        string Operator1 = sqlReader.GetValue(1).ToString();
                        string Value1 = sqlReader.GetValue(2).ToString();
                        string Operator2 = sqlReader.GetValue(3).ToString();
                        string Value2 = sqlReader.GetValue(4).ToString();
                        string tmpFixDisc = sqlReader.GetValue(5).ToString();
                        string FixDisc = (string.IsNullOrEmpty(tmpFixDisc) || tmpFixDisc == "0") ? "No" : "Yes";
                        string Scenario = sqlReader.GetValue(6).ToString();

                        List<string> Conds = new List<string>(ControlList(IDControl));
                        string Condition1 = (string.IsNullOrEmpty(Operator1)) ? "" : Conds[0] + " " + Operator1 + " " + Value1;
                        string Condition2 = (string.IsNullOrEmpty(Operator2)) ? "" : Conds[1] + " " + Operator2 + " " + Value2;

                        TRClass = ((idx % 2) == 1) ? "Class='odd'" : "Class='even'";
                        string TRStart = "<tr " + TRClass + "><td>";

                        string tmpRow = TRStart + IDControl + TDEnd;
                        tmpRow += TDStartLeft + RuleControl(IDControl) + TDEnd;
                        tmpRow += TDStartLeft + Condition1 + TDEnd;
                        tmpRow += TDStartLeft + Condition2 + TDEnd;
                        tmpRow += TDStart + FixDisc + TDEnd;
                        tmpRow += TDStart + Scenario + TDEnd;
                        tmpRow += TDStart + Imgs + TREnd;

                        Rows += tmpRow;
                    }
                    result = Rows;
                }
            }
            return result;
        }

        private static List<string> ControlList(string IDControl){
            List<string> result = new List<string>();

            string ControlPrefix = IDControl.Substring(0, 2);
            switch (ControlPrefix){
                case "CH":
                    result.Add("Channel");
                    break;
                case "DC":
                    result.Add("Discount");
                    break;
                case "CD":
                    result.Add("Channel");
                    result.Add("Discount");
                    break;
            }
            return result;
        }

        private static string RuleControl(string IDControl){
            string result = "";
            string RulePrefix = IDControl.Substring(0, 2);
            switch (RulePrefix){
                case "CH":
                    result = "CHANNEL";
                    break;
                case "DC":
                    result = "DISCOUNT";
                    break;
                case "CD":
                    result = "CHANNEL + DISCOUNT";
                    break;
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<string> ExtRuleInfo(string RuleCategory, string RuleValue, 
            string RuleScenario){

            List<string> result = new List<string>();
            string sCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(sCon);

            int Scenario = int.Parse(RuleScenario);
            using (cn){
                cn.Open();

                string qry = "SELECT KeyLayer, TotalLayer, Description FROM tbl_ApprovalRule " +
                    "WHERE (IDControl = @IDControl) AND (Value1 = @Value1) AND (Scenario = @Scenario)";

                SqlCommand cmd = new SqlCommand(qry, cn);
                cmd.Parameters.AddWithValue("@IDControl", RuleCategory);
                cmd.Parameters.AddWithValue("@Value1", RuleValue);
                cmd.Parameters.AddWithValue("@Scenario", Scenario);

                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string KeyLayer = rdr.GetValue(0).ToString();
                        string TotalLayer = rdr.GetValue(1).ToString();
                        string Description = rdr.GetValue(2).ToString();

                        result.Add(KeyLayer);
                        result.Add(TotalLayer);
                        result.Add(Description);
                    }
                }
                cn.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> GetLayerList(string RuleCtrl, string RuleValue, 
            string RuleScenario){
            List<object> result = new List<object>();
            string _RuleCtrl = Func.Sanitize(RuleCtrl);
            string _RuleValue = Func.Sanitize(RuleValue);
            string tmpScenario = Func.Sanitize(RuleScenario);
            int _RuleScenario = Convert.ToInt32(RuleScenario);

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));

            int MaxStep = 0;
            int tmpMaxStep = GetMaxStep(_RuleCtrl, _RuleValue, _RuleScenario);
            if (tmpMaxStep > 0){
                MaxStep = tmpMaxStep;
            }else{
                MaxStep = getMaxLayers(_RuleCtrl, _RuleValue, _RuleScenario);
            }

            using (cn){
                for (int idx = 0; idx < MaxStep; idx++){
                    int intStep = (idx + 1);

                    cn.Open();
                    string qry = "SELECT StepSeq, CurrentStep, ActorCategory, (CASE WHEN " +
                        "ActorCategory = 'ROLE' THEN (SELECT RoleName FROM mstRole " +
                        "WHERE IDRole = tbl_ApprovalStep.CurrentStep) ELSE " +
                        "(SELECT FullName FROM UserAccounts WHERE UserName = tbl_ApprovalStep.CurrentStep) " +
                        "END) AS RoleName FROM tbl_ApprovalStep " +
                        "WHERE (IDControl = '" + _RuleCtrl + "') AND (Value1 = '" + _RuleValue + "') AND " +
                        "(Scenario = " + _RuleScenario + ") AND (StepSeq = " + intStep + ") " +
                        "GROUP BY StepSeq, CurrentStep, ActorCategory ORDER BY StepSeq";
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        List<object> tmpList = new List<object>();
                        while (rdr.Read()){
                            Dictionary<string, object> DictRules = new Dictionary<string, object>();
                            DictRules["CurrentStep"] = rdr.GetValue(1).ToString();
                            DictRules["ActorCategory"] = rdr.GetValue(2).ToString();
                            DictRules["ActorName"] = rdr.GetValue(3).ToString();

                            tmpList.Add(DictRules);
                        }
                        Dictionary<string, object> stepList = new Dictionary<string, object>();
                        stepList.Add("Seq", intStep);
                        stepList.Add("RuleID", _RuleCtrl);
                        stepList.Add("Scenario", _RuleScenario);
                        stepList.Add("Details", tmpList);

                        result.Add(stepList);
                    }
                    cn.Close();
                }
            }
            return result;
        }

        private static int GetMaxStep(string RuleCtrl, string RuleValue, int RuleScenario){
            int Result = 0;

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string qry = "SELECT MAX(StepSeq) AS MaxStep FROM tbl_ApprovalStep " +
                "WHERE (IDControl = '" + RuleCtrl + "') AND (Value1 = '" + RuleValue + "') AND " +
                "(Scenario = " + RuleScenario + ")";

            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string strMaxStep = rdr.GetValue(0).ToString();
                        if (string.IsNullOrEmpty(strMaxStep)){
                            Result = 0;
                        }else{
                            Result = Convert.ToInt32(strMaxStep);
                        }
                    }
                    cn.Close();
                }
            }
            return Result;
        }

        private static int getMaxLayers(string RuleCtrl, string RuleValue, int RuleScenario){
            int Result = 0;

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string qry = "SELECT TotalLayer FROM tbl_ApprovalRule " +
                "WHERE (IDControl = '" + RuleCtrl + "') AND (Value1 = '" + RuleValue + "') AND " +
                "(Scenario = " + RuleScenario + ")";
            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string strMaxLayers = rdr.GetValue(0).ToString();
                        Result = Convert.ToInt32(strMaxLayers) + 1;
                    }
                    cn.Close();
                }
            }
            return Result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string DeleteRuleScenario(string RuleCategory, string RuleValue, 
            string RuleScenario){

            string result = "";
            string _RuleCtrl = Func.Sanitize(RuleCategory);
            string _RuleValue = Func.Sanitize(RuleValue);
            string tmpScenario = Func.Sanitize(RuleScenario);
            int _RuleScenario = Convert.ToInt32(RuleScenario);

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                string qry = "DELETE FROM tbl_ApprovalRule WHERE (IDControl = @IDControl) AND " +
                    "(Value1 = @Value1) AND (Scenario = @Scenario)";
                SqlCommand cmd = new SqlCommand(qry, cn);
                cmd.Parameters.AddWithValue("@IDControl", _RuleCtrl);
                cmd.Parameters.AddWithValue("@Value1", _RuleValue);
                cmd.Parameters.AddWithValue("@Scenario", _RuleScenario);

                int DelField = cmd.ExecuteNonQuery();
                if (DelField > 0){
                    if (GetMaxStep(_RuleCtrl, _RuleValue, _RuleScenario) > 0){
                        int DelSubField = DeleteRuleSteps(_RuleCtrl, _RuleValue, _RuleScenario);
                        if (DelSubField > 0){
                            result = "success";
                        }else{
                            result = "Delete data failed!";
                        }
                    }else{
                        result = "success";
                    }
                }else{
                    result = "Delete data failed!";
                }
                cn.Close();
            }
            return result;
        }

        private static int DeleteRuleSteps(string RuleCategory, string RuleValue, int RuleScenario){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                string qry = "DELETE FROM tbl_ApprovalStep WHERE (IDControl = @IDControl) AND " +
                    "(Value1 = @Value1) AND (Scenario = @Scenario)";
                SqlCommand cmd = new SqlCommand(qry, cn);
                cmd.Parameters.AddWithValue("@IDControl", RuleCategory);
                cmd.Parameters.AddWithValue("@Value1", RuleValue);
                cmd.Parameters.AddWithValue("@Scenario", RuleScenario);

                result = cmd.ExecuteNonQuery();

                cn.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> LoadRole(string Search){
            List<object> result = new List<object>();
            string _Search = Func.Sanitize(Search);
            string strWhere = (string.IsNullOrEmpty(Search)) ? "" : " AND (RoleAlias LIKE '%" + _Search + "%')";

            string strQry = "SELECT IDRole, RoleAlias, RoleName FROM mstRole WHERE " +
                "(IDRole NOT IN (1,2))" + strWhere + " ORDER BY RoleAlias";
            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    while (sqlReader.Read()){
                        string strRoleID = sqlReader.GetValue(0).ToString();
                        string strRoleAlias = sqlReader.GetValue(1).ToString();
                        string strRoleName = sqlReader.GetValue(2).ToString();

                        Dictionary<string, object> dctRole = new Dictionary<string, object>();
                        dctRole.Add("ID", strRoleID);
                        dctRole.Add("Role", strRoleAlias);
                        dctRole.Add("Name", strRoleName);

                        result.Add(dctRole);
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> LoadUser(string Search){
            List<object> result = new List<object>();
            string _Search = Func.Sanitize(Search);
            string strWhere = (string.IsNullOrEmpty(Search)) ? "" : "AND (UserAccounts.FullName LIKE '%" + _Search + "%') ";

            string strQry = "SELECT UserAccounts.UserName, UserAccounts.FullName, mstRole.RoleAlias " +
                "FROM UserAccounts INNER JOIN mstRole ON UserAccounts.IDRole = mstRole.IDRole " +
                "WHERE (UserAccounts.IDRole NOT IN (1, 2)) AND (UserAccounts.StatusCode = '101') " + strWhere +
                "ORDER BY UserAccounts.FullName";
            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    List<string> lstUsers = new List<string>();
                    while (sqlReader.Read()){
                        string strRole = sqlReader.GetValue(2).ToString();
                        string strUserName = sqlReader.GetValue(0).ToString();
                        string strFName = sqlReader.GetValue(1).ToString();

                        Dictionary<string, object> dctUsers = new Dictionary<string, object>();
                        dctUsers.Add("ID", strUserName);
                        dctUsers.Add("Role", strRole);
                        dctUsers.Add("Name", strFName);

                        result.Add(dctUsers);
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod(EnableSession = true)]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string UpdateLayer(string RuleControl, string RuleValue, string RuleValueExt, string RuleScenario,
            List<string> StepSeq, List<string> CurrentStep, List<string> NextStep,
            List<string> ActorCategory){

            string result = "";
            string[] tmpStepSeq = StepSeq[0].Split(',');
            string[] tmpCurrentStep = CurrentStep[0].Split(',');
            string[] tmpNextStep = NextStep[0].Split(',');
            string[] tmpActorCategory = ActorCategory[0].Split(',');

            string _RuleControl = Func.Sanitize(RuleControl);
            string _RulePrefix = _RuleControl.Substring(0, 2);
            string _RuleValue = Func.Sanitize(RuleValue);
            string _RuleValueExt = Func.Sanitize(RuleValueExt);
            int _RuleScenario = Convert.ToInt32(Func.Sanitize(RuleScenario));

            int StepLen = tmpStepSeq.Length;
            int NewMaxSteps = Convert.ToInt32(tmpStepSeq[(StepLen - 1)]) - 1;
            bool updateStat = false;
            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                sqlCon.Open();
                // Delete all step category with same scenario
                string strQry1 = "DELETE FROM tbl_ApprovalStep WHERE (IDControl = @IDControl) AND " +
                    "(Value1 = @Value1) AND (Scenario = @Scenario)";
                SqlCommand sqlCmd1 = new SqlCommand(strQry1, sqlCon);
                sqlCmd1.Parameters.AddWithValue("@IDControl", _RuleControl);
                sqlCmd1.Parameters.AddWithValue("@Value1", _RuleValue);
                sqlCmd1.Parameters.AddWithValue("@Scenario", _RuleScenario);
                sqlCmd1.ExecuteNonQuery();
                sqlCon.Close();
                // End Delete Process

                //re-Inserting data
                string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);
                string CreatedDate = DateTime.Today.ToString("yyyy/MM/dd HH:mm:ss");

                for (int idx = 0; idx < StepLen; idx++){
                    sqlCon.Open();

                    string strQry2 = "INSERT INTO tbl_ApprovalStep (ControlPrefix, IDControl, " +
                        "Value1, Value2, Scenario, StepSeq, CurrentStep, NextStep, ActorCategory, " +
                        "CreatedBy, CreatedDate) VALUES (";

                    string insertValues = "'" + _RulePrefix + "', '" +
                        _RuleControl + "', '" + _RuleValue + "', '" + _RuleValueExt + "', " + _RuleScenario + ", " +
                        tmpStepSeq[idx] + ", '" + tmpCurrentStep[idx] + "', '" +
                        tmpNextStep[idx] + "', '" + tmpActorCategory[idx] + "', '" +
                        CreatedBy + "', '" + CreatedDate + "')";

                    strQry2 += insertValues;

                    SqlCommand sqlCmd2 = new SqlCommand(strQry2, sqlCon);
                    int AffectedRows = sqlCmd2.ExecuteNonQuery();
                    if (!(AffectedRows > 0)){
                        updateStat = false;
                    }else{
                        updateStat = true;
                    }
                    sqlCon.Close();
                }
            }

            if (updateStat == true){
                int intUpdate = UpdateMaxLayer(NewMaxSteps, _RuleControl, _RuleValue, _RuleScenario);
                if (intUpdate >= 0){
                    result = "success";
                }else{
                    result = "Update data failed!";
                }
            }else{
                result = "Update data failed!";
            }
            return result;
        }

        private static int UpdateMaxLayer(int TotalLayers, string RuleControl, 
            string RuleValue, int RuleScenario){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                string qry = "UPDATE tbl_ApprovalRule SET TotalLayer = @TotalLayer " +
                    "WHERE (IDControl = @IDControl) AND (Value1 = @Value1) AND (Scenario = @Scenario)";
                SqlCommand cmd = new SqlCommand(qry, cn);
                cmd.Parameters.AddWithValue("@TotalLayer", TotalLayers);
                cmd.Parameters.AddWithValue("@IDControl", RuleControl);
                cmd.Parameters.AddWithValue("@Value1", RuleValue);
                cmd.Parameters.AddWithValue("@Scenario", RuleScenario);

                result = cmd.ExecuteNonQuery();

                cn.Close();
            }
            return result;
        }
    }
}
